﻿//====================================================================
// Create by Daniel
// file： YCUserLog.cs
// project ：YunCMS
// Build time：2012-09-22
// modify:
// description:
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using YunCMS.Model;
using YunCMS.IDAL;
//using Utility;

namespace YunCMS.DAL
{
    /// <summary>
    /// Data Access Layer of dbo.YC_User_Log.
    /// </summary>
    public partial class YCUserLogDAL : IYCUserLogDAL
    {
        private const string TABLE = "YC_User_Log";
        #region

        /// <summary>
        /// constractor
        /// </summary>
        public YCUserLogDAL()
        {
        }
        #endregion

        #region -----------Methods-----------
        /// <summary>
        /// Insert a model to Database
        /// </summary>
        /// <param name="model">YCUserLognew model</param>
        /// <returns>The new item Id</returns>
        public int Add(YCUserLog model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert into YC_User_Log(");
            sqlStr.Append("UserID,UserName,Action,ActionTime,IPAddress,Note,Module)");
            sqlStr.Append(" values (");
            sqlStr.Append("@UserID,@UserName,@Action,@ActionTime,@IPAddress,@Note,@Module)");
            sqlStr.Append(";select @@IDENTITY");
            int res;

            res = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, false));
            return res;
        }
        /// <summary>
        /// Insert a model to Database in transaction.
        /// </summary>
        /// <param name="trans">SqlTransaction</param>
        /// <param name="model">YCUserLognew model</param>
        /// <returns>The new item Id</returns>
        public int Add(SqlTransaction trans, YCUserLog model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert into YC_User_Log(");
            sqlStr.Append("UserID,UserName,Action,ActionTime,IPAddress,Note,Module)");
            sqlStr.Append(" values (");
            sqlStr.Append("@UserID,@UserName,@Action,@ActionTime,@IPAddress,@Note,@Module)");
            int res;

            res = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, false));
            return res;
        }        

        /// <summary>
        /// delete a model from db
        /// </summary>
        /// <param name="id">ID</param>
        /// <returns>rows affected</returns>
        public int Delete(long id)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("delete YC_User_Log ");
            sqlStr.Append(" where ID=@ID");
            SqlParameter[] parameters ={
							new SqlParameter("@ID",SqlDbType.BigInt),
			
			};
            parameters[0].Value = id;
            return SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), parameters);
        }

        /// <summary>
        /// delete a model from db in transaction
        /// </summary>        
        /// <param name="trans">SqlTransaction</param>
        /// <param name="id">ID</param>
        /// <returns>rows affected</returns>
        public int Delete(SqlTransaction trans, long id)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("delete YC_User_Log ");
            sqlStr.Append(" where ID=@ID");
            SqlParameter[] parameters ={
							new SqlParameter("@ID",SqlDbType.BigInt),
			
			};
            parameters[0].Value = id;
            return SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlStr.ToString(), parameters);
        }

        /// <summary>
        ///Get YCUserLog Entity
        /// </summary>
        /// <param name="dr">dr</param>
        /// <returns>YCUserLog Entity</returns>
        public YCUserLog PopulateYCUserLogFromDr(IDataReader dr)
        {
            YCUserLog Obj = new YCUserLog();

            Obj.ID = Convert.ToInt64(dr["ID"]);
            Obj.UserID = Convert.ToInt32(dr["UserID"]);
            Obj.UserName = dr["UserName"].ToString();
            Obj.Action = dr["Action"].ToString();
            if ((dr["ActionTime"]) == DBNull.Value) { Obj.ActionTime = null; } else { Obj.ActionTime = Convert.ToDateTime(dr["ActionTime"]); }
            Obj.IPAddress = dr["IPAddress"].ToString();
            Obj.Note = dr["Note"].ToString();
            Obj.Module = dr["Module"].ToString();

            return Obj;
        }

        /// <summary>
        /// get a YCUserLog object 
        /// </summary>
        /// <param name=id>ID</param>
        /// <returns>YCUserLog object</returns>
        public YCUserLog GetYCUserLog(long id)
        {
            YCUserLog _obj = null;
            SqlParameter[] parameters ={
			new SqlParameter("@ID",SqlDbType.BigInt)
			};
            parameters[0].Value = id;
            string sqlStr = "select * from YC_User_Log where ID=@ID";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr, parameters))
            {
                while (dr.Read())
                {
                    _obj = PopulateYCUserLogFromDr(dr);
                }
            }
            return _obj;
        }
        /// <summary>
        /// Get YCUserLogs List
        /// </summary>
        /// <returns>List</returns>
        public IList<YCUserLog> GetList(string sqlStr)
        {
            IList<YCUserLog> Obj = new List<YCUserLog>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr))
            {
                while (dr.Read())
                {
                    Obj.Add(PopulateYCUserLogFromDr(dr));
                }
            }
            return Obj;
        }

        private string parseQueryStr(DateTime? start, DateTime? end)
        {
            string checkQuery = "";
            string topQuery = "";
            if (start != null)
            {
                string startDate = start.Value.ToString("yyyy-MM-dd");
                checkQuery = " and ActionTime>'" + startDate + "'";
            }
            if (end != null)
            {
                string endDate = end.Value.ToString("yyyy-MM-dd");
                topQuery = " and ActionTime<'" + endDate + "'";
            }

            return string.Format("{0}{1}", checkQuery, topQuery);
        }

        public IList<YCUserLog> GetList(string name, DateTime? start, DateTime? end, int pageSize, int pageCurrent)
        {
            string queryWhere = parseQueryStr(start, end);
            IList<YCUserLog> Obj = new List<YCUserLog>();
            SqlParameter[] parameters ={
                new SqlParameter("@TableName",SqlDbType.VarChar),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@PageCurrent",SqlDbType.Int),
                new SqlParameter("@FdShow",SqlDbType.NVarChar),
                new SqlParameter("@FdOrder",SqlDbType.NVarChar),
                new SqlParameter("@Name",SqlDbType.NVarChar),
                new SqlParameter("@QueryWhere",SqlDbType.NVarChar)
            };
            parameters[0].Value = TABLE;
            parameters[1].Value = pageSize;
            parameters[2].Value = pageCurrent;
            parameters[3].Value = "*";
            parameters[4].Value = "ActionTime desc";
            parameters[5].Value = name;
            parameters[6].Value = queryWhere;

            string storeStri = "YC_Log_Paging_Proc";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, storeStri, parameters))
            {
                while (dr.Read())
                {
                    Obj.Add(PopulateYCUserLogFromDr(dr));
                }
            }
            return Obj;
        }

        public int GetCount(string name, DateTime? start, DateTime? end)
        {
            string queryWhere = parseQueryStr(start, end);
            SqlParameter[] parameters ={
              new SqlParameter("@TableName",SqlDbType.VarChar),
                            new SqlParameter("@docount",SqlDbType.Bit),
                            new SqlParameter("@Name",SqlDbType.NVarChar),
                            new SqlParameter("@QueryWhere",SqlDbType.NVarChar)
            };
            parameters[0].Value = TABLE;
            parameters[1].Value = true;
            parameters[2].Value = name;
            parameters[3].Value = queryWhere;

            string storeStri = "YC_Log_Paging_Proc";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, storeStri, parameters))
            {
                if (dr.Read())
                {
                    return dr.GetInt32(0);
                }
            }
            return 0;
        }

        /// <summary>
        /// if exist a YCUserLog according by ID
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>true/false</returns>
        public bool IsExist(long id)
        {
            SqlParameter[] parameters ={
                                      new SqlParameter("@ID",SqlDbType.BigInt)
                                  };
            parameters[0].Value = id;
            string sqlStr = "select Count(1) from YC_User_Log where ID=@ID";
            int a = Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, sqlStr, parameters));
            if (a > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        private SqlParameter[] PrepareSqlParams(YCUserLog model, bool isUpdate)
        {
            List<SqlParameter> parameters = new List<SqlParameter>() {							
							new SqlParameter("@UserID",SqlDbType.Int),
							new SqlParameter("@UserName",SqlDbType.NVarChar),
							new SqlParameter("@Action",SqlDbType.VarChar),
							new SqlParameter("@ActionTime",SqlDbType.DateTime),
							new SqlParameter("@IPAddress",SqlDbType.VarChar),
							new SqlParameter("@Note",SqlDbType.NVarChar),
							new SqlParameter("@Module",SqlDbType.VarChar)
			};
            parameters[0].Value = model.UserID;
            if (model.UserName != String.Empty)
                parameters[1].Value = model.UserName;
            else
                parameters[1].Value = DBNull.Value;
            if (model.Action != String.Empty)
                parameters[2].Value = model.Action;
            else
                parameters[2].Value = DBNull.Value;
            if (model.ActionTime != Convert.ToDateTime("0001-1-1"))
                parameters[3].Value = model.ActionTime;
            else
                parameters[3].Value = DBNull.Value;
            if (model.IPAddress != String.Empty)
                parameters[4].Value = model.IPAddress;
            else
                parameters[4].Value = DBNull.Value;
            if (model.Note != String.Empty)
                parameters[5].Value = model.Note;
            else
                parameters[5].Value = DBNull.Value;
            if (model.Module != String.Empty)
                parameters[6].Value = model.Module;
            else
                parameters[6].Value = DBNull.Value;

            if (isUpdate)
            {
                SqlParameter param = new SqlParameter("@ID", SqlDbType.BigInt);
                param.Value = model.ID;
                parameters.Add(param);
            }

            return parameters.ToArray();
        }

        #endregion
    }
}




